package com.css.fxfzcpxx.modules.informationAchievement.repository.impl;

import com.css.fxfzcpxx.constants.YNEnum;
import com.css.fxfzcpxx.modules.categoryView.param.QueParam;
import com.css.fxfzcpxx.modules.categoryView.vo.CategoryViewVo;
import com.css.fxfzcpxx.modules.informationAchievement.entity.InfAchievement;
import com.css.fxfzcpxx.modules.informationAchievement.param.DisasterLossAssessmentParam;
import com.css.fxfzcpxx.modules.informationAchievement.param.QueParams;
import com.css.fxfzcpxx.modules.informationAchievement.repository.InfAchievementRepository;
import com.css.fxfzcpxx.modules.informationAchievement.vo.ExportVo;
import com.css.fxfzcpxx.modules.informationAchievement.vo.InfAchievementVo;
import com.css.fxfzcpxx.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class InfAchievementRepositoryImpl implements InfAchievementRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "cpxx_information_achievement";

    @Override
    public Map<String, Object> getPage(QueParams queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from "+TABLE_NAME+"");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '"+ YNEnum.N.toString()+"'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if (!PlatformObjectUtils.isEmpty(queParams.getReleaseStatus())) {
            whereSql.append(" and release_status = '"+queParams.getReleaseStatus()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getAchievementName())) {
            whereSql.append(" and achievement_name like '%" + queParams.getAchievementName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getAchievementColumn())) {
            whereSql.append(" and achievement_column like '%" + queParams.getAchievementColumn() + "%'");
        }
        /*if (!PlatformObjectUtils.isEmpty(queParams.getStartTime())&!PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and achievement_date between '"+queParams.getStartTime()+"' and '"+queParams.getEndTime()+"'");
        }*/

        //时间范围
        if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & !PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and Date(achievement_date) between '" + queParams.getStartTime() + "' and '" + queParams.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and achievement_date  >='" + queParams.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(queParams.getEndTime()) & PlatformObjectUtils.isEmpty(queParams.getStartTime())) {
            whereSql.append("and achievement_date  <='" + queParams.getEndTime() + "'");
        }


        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(InfAchievement.class)).size();
        List<InfAchievement> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(InfAchievement.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public void save(InfAchievement infAchievement) {
        String sql = "insert into "+TABLE_NAME+"(id,achievement_name,release_status,achievement_column,achievement_describe,achievement_upload,appendix,keyword," +
                "remark,create_user,create_time,update_user,update_time,del_flag,achievement_date,shock_time,publishing_unit,data_origin) " +
                "values(:id, :achievementName, :releaseStatus, :achievementColumn, :achievementDescribe, :achievementUpload, :appendix, :keyword, " +
                ":remark, :createUser, :createTime, :updateUser, :updateTime, :delFlag, :achievementDate,:shockTime,:publishingUnit,:dataOrigin)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(infAchievement);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void recallOrPublish(String releaseStatus, List<String> ids, Date date) {
        String sql = "update "+TABLE_NAME+" set release_status = '"+releaseStatus+"',achievement_date = '"+date+"' where id in  ('" + String.join("','", ids) + "')";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delete(List<String> ids) {
        String sql = "update "+TABLE_NAME+" set del_flag = '"+YNEnum.Y.toString()+"' where id in  ('" + String.join("','", ids) + "')";
        jdbcTemplate.update(sql);
    }

    @Override
    public InfAchievement findById(String id) {
        String sql = "select * from "+TABLE_NAME+" where id = ? ";
        InfAchievement infAchievement = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(InfAchievement.class), id);
        return infAchievement;
    }

    @Override
    public List<InfAchievement> getkeywordManageData(List<String> ids) {
        String sql = "select * from "+TABLE_NAME+" where id in  ('" + String.join("','", ids) + "')";
        List<InfAchievement> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InfAchievement.class));
        return list;
    }

    @Override
    public void keywordUpdate(String id, String keyword) {
        String sql = "update "+TABLE_NAME+" set keyword = ? where id = ?";
        jdbcTemplate.update(sql,keyword,id);
    }

    @Override
    public void publishedDataupdate(InfAchievement infAchievement) {
        String sql = "update "+TABLE_NAME+" set keyword= :keyword ," +
                "update_user = :updateUser ,update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(infAchievement);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void unpublishedDataupdate(InfAchievement infAchievement) {
        String sql = "update "+TABLE_NAME+" set achievement_name= :achievementName ,achievement_column= :achievementColumn ," +
                "achievement_describe = :achievementDescribe ,achievement_upload= :achievementUpload ,appendix = :appendix ,shock_time=:shockTime," +
                "keyword= :keyword ,remark = :remark ,update_user = :updateUser ,update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(infAchievement);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public Map<String, Object> disasterLossAssessment(DisasterLossAssessmentParam queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select a.*,b.attach_name,b.attach_path from (select * from cpxx_information_achievement ");
        StringBuilder whereSql = new StringBuilder(" where release_status ='0' and del_flag = '"+ YNEnum.N.toString()+"'");
        StringBuilder orderSql = new StringBuilder(" order by a.achievement_date desc");
        int firstIndex = (curPage - 1) * pageSize;
        //StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        String limitSql = String.format(" limit %d  offset %d ", pageSize, firstIndex);
        //名称
        if (!PlatformObjectUtils.isEmpty(queParams.getAchievementName())) {
            whereSql.append(" and achievement_name like '%" + queParams.getAchievementName() + "%'");
        }
        //成果栏目
        if (!PlatformObjectUtils.isEmpty(queParams.getAchievementColumn())) {
            whereSql.append(" and achievement_column like '%" + queParams.getAchievementColumn() + "%'");
        }
        //关键字
        if (!PlatformObjectUtils.isEmpty(queParams.getKeyword())) {
            whereSql.append(" and keyword like '%" + queParams.getKeyword() + "%'");
        }
        //时间范围
        if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & !PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and Date(achievement_date) between '" + queParams.getStartTime() + "' and '" + queParams.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and Date(achievement_date)  >='" + queParams.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(queParams.getEndTime()) & PlatformObjectUtils.isEmpty(queParams.getStartTime())) {
            whereSql.append("and Date(achievement_date)  <='" + queParams.getEndTime() + "'");
        }
        //发震时间范围
        if (!PlatformObjectUtils.isEmpty(queParams.getStartShockTime()) & !PlatformObjectUtils.isEmpty(queParams.getEndShockTime())) {
            whereSql.append("and Date(shock_time) between '" + queParams.getStartShockTime() + "' and '" + queParams.getEndShockTime() + "'");
        }
        //大于开始发震时间
        if (!PlatformObjectUtils.isEmpty(queParams.getStartShockTime()) & PlatformObjectUtils.isEmpty(queParams.getEndShockTime())) {
            whereSql.append("and Date(shock_time)  >='" + queParams.getStartShockTime() + "'");
        }
        //小于结束发震时间
        if ( !PlatformObjectUtils.isEmpty(queParams.getEndShockTime()) & PlatformObjectUtils.isEmpty(queParams.getStartShockTime())) {
            whereSql.append("and Date(shock_time)  <='" + queParams.getEndShockTime() + "'");
        }
        StringBuilder sqlb = new StringBuilder(" ) a left join (select * from sys_attachment_info )b on a.achievement_upload=b.attach_id ");
        sql.append(whereSql).append(sqlb).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(InfAchievementVo.class)).size();
        List<InfAchievementVo> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(InfAchievementVo.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<String> getDataByOrigin(String sign) {
        String sql = "select id from "+TABLE_NAME+" where data_origin='"+sign+"'";
        List<String> list = jdbcTemplate.queryForList(sql, String.class);
        return list;
    }

    @Override
    public List<CategoryViewVo> getData(QueParam queParam) {
        StringBuilder sql = new StringBuilder("select achievement_name meansName,achievement_column module2," +
                "achievement_date publishingDate,create_time,achievement_upload attachId,data_origin from "+TABLE_NAME+"");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '"+ YNEnum.N.toString()+"' and release_status='0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");


        if (!PlatformObjectUtils.isEmpty(queParam.getKeyword())) {
            whereSql.append(" and achievement_name like '%" + queParam.getKeyword() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParam.getModule2())) {
            whereSql.append(" and achievement_column like '%" + queParam.getModule2() + "%'");
        }

        //时间范围
        if (!PlatformObjectUtils.isEmpty(queParam.getStartTime()) & !PlatformObjectUtils.isEmpty(queParam.getEndTime())) {
            whereSql.append("and achievement_date between '" + queParam.getStartTime() + "' and '" + queParam.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(queParam.getStartTime()) & PlatformObjectUtils.isEmpty(queParam.getEndTime())) {
            whereSql.append("and achievement_date  >='" + queParam.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(queParam.getEndTime()) & PlatformObjectUtils.isEmpty(queParam.getStartTime())) {
            whereSql.append("and achievement_date  <='" + queParam.getEndTime() + "'");
        }

        List<CategoryViewVo> list = jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(CategoryViewVo.class));

        return list;
    }

    @Override
    public Integer getDownload(String unit) {
        String sql = "select sum(b.download_count) from "+TABLE_NAME+" a  " +
                "inner join  sys_attachment_record b on a.achievement_upload=b.attachment_id " +
                "where a.del_flag='0' and EXTRACT("+unit+" FROM b.download_time) = EXTRACT("+unit+" FROM CURRENT_DATE) ";
        Integer total = jdbcTemplate.queryForObject(sql, Integer.class);
        if(PlatformObjectUtils.isEmpty(total)){
            return 0;
        }
        return total;
    }

    @Override
    public List<InfAchievementVo> getLatestData() {
        String sql="select * from "+TABLE_NAME+" where del_flag='0' and release_status='0' order by create_time desc limit 8";
        List<InfAchievementVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InfAchievementVo.class));
        return list;
    }

    @Override
    public List<ExportVo> getExportData(List<String> idList) {
        String sql = "select *,achievement_date::date as achievementDateStr from "+TABLE_NAME+" where id in  (:ids)";
        Map idMap = new HashMap();
        idMap.put("ids", idList);
        List<ExportVo> list = namedParameterJdbcTemplate.query(sql, idMap,new BeanPropertyRowMapper<>(ExportVo.class));
        return list;
    }
}
